﻿Option Strict On
Imports System.Data.OleDb
Imports System.IO
Imports ADOX
Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Access
Imports OleDbConnection = System.Data.OleDb.OleDbConnection

Module 手集計

    Public Sub T300_E判定(ETable As String, E採用判定値 As Double)
        DEL_table("T300_E*")
        'T160_全出荷E集計からT300_E作成
        MySQL = "SELECT
                    " & ETable & ".順位,
                    " & ETable & ".出荷先,
                    " & ETable & ".出荷先 AS 出荷先1,
                    " & ETable & ".乱数,
                    '' AS 判定,
                    NULL AS ランク,
                    '" & ETable & "' AS ETable
                INTO
                    T300_E判定
                FROM
                    " & ETable & ";"
        SQL実行(MySQL)

        'T300_E作成へT160_全出荷E集計追加（発送先＝‘A’& 発送先 ,乱数=乱数+1）
        MySQL = "INSERT INTO T300_E判定 ( 順位, 出荷先, 出荷先1, 乱数, 判定, ランク, ETable ) 
                    SELECT " & ETable & ".順位, " & ETable & ".出荷先, 'A' & [出荷先] AS 出荷先1, [乱数]+1 AS 乱数1, 
                        '' AS 判定, '未定' AS ランク, '" & ETable & "' AS ETable
                    FROM " & ETable & ";"
        SQL実行(MySQL)

    End Sub
    Public Sub T300_I判定(ITable As String, I採用判定値 As Double)
        DEL_table("T300_I*")
        'T160_全出荷I集計からT300_I作成
        MySQL = "SELECT " & ITable & ".順位, 
                        " & ITable & ".[アイテム], 
                        " & ITable & ".[アイテム] AS アイテム1, 
                        " & ITable & ".乱数, 0.9 AS I採用判定値, 
                    IIf([" & ITable & "]![乱数]<=0.9,'Yes','No') AS I判定, '未定' AS ランク, '" & ITable & "' AS ITable
                    INTO T300_I判定
                    FROM " & ITable & ";"
        SQL実行(MySQL)

        'T300_I作成へT160_全出荷I集計追加（アイテム＝‘A’& アイテム ,乱数=乱数+1）
        MySQL = "INSERT INTO T300_I判定 ( 順位, アイテム, アイテム1, 乱数, I判定, ランク, ITable ) 
                    SELECT " & ITable & ".順位, " & ITable & ".[アイテム], 'A' & [アイテム] AS アイテム1, [乱数]+1 AS 乱数1, 
                        IIf([" & ITable & "]![乱数]+1<=" & I採用判定値 & ",'Yes','No') AS I判定, '未定' AS ランク, '" & ITable & "' AS ITable 
                    FROM " & ITable & ";"
        SQL実行(MySQL)

    End Sub
    Public Sub T800_計算対象作成()
        DEL_table("T780*")
        DEL_table("T800*")
        'T780_採用出荷先作成(T200からT300_E判定.判定) = 'Yes'よりデータ抽出)
        MySQL = "SELECT
                        T200.行連番,
                        T300_E判定.出荷先1,
                        T200.アイテム,
                        T200.G行,
                        T200.Gバ,
                        T200.Gケ,
                        T200.GPL,
                        T200.G容,
                        T200.G重
                    INTO
                        T780_採用出荷先
                    FROM
                        T300_E判定
                        INNER JOIN T200 ON T300_E判定.出荷先 = T200.出荷先
                    WHERE
                        (((T300_E判定.判定) = 'Yes'));"
        SQL実行(MySQL)

        'T800_計算対象作成（T780_採用出荷先からT300_I判定.I判定) = 'Yes'のデータを抽出）
        MySQL = "SELECT
                        T780_採用出荷先.行連番,
                        T780_採用出荷先.出荷先1 as 出荷先,
                        T300_I判定.[アイテム1] as アイテム,
                        T780_採用出荷先.G行,
                        T780_採用出荷先.Gバ,
                        T780_採用出荷先.Gケ,
                        T780_採用出荷先.GPL,
                        T780_採用出荷先.G容,
                        T780_採用出荷先.G重
                    INTO
                        T800_計算対象
                    FROM
                        T300_I判定
                        LEFT JOIN T780_採用出荷先 ON T300_I判定.[アイテム] = T780_採用出荷先.[アイテム]
                    WHERE
                        (((T300_I判定.I判定) = 'Yes'));"
        SQL実行(MySQL)
    End Sub
    Public Sub T860_全出荷E集計()
        ' ===== 1. T860* テーブルを削除 =====
        DEL_table("T860_全出荷E*")

        ' ===== 2. T860_全出荷E集計 を 出荷先 × GPL合計 で作成 =====
        MySQL = "SELECT 出荷先, Sum(GPL) AS GPL合計 INTO [T860_全出荷E集計] " &
                "FROM T800_計算対象 " &
                "GROUP BY 出荷先"
        SQL実行(MySQL)

        ' ===== 3. GPLEランク 列を追加 =====
        Try
            Using cn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & MyDB & ";")
                cn.Open()
                Using addCmd As New OleDbCommand(
                    "ALTER TABLE [T860_全出荷E集計] ADD COLUMN [GPLEランク] TEXT(10)", cn)
                    addCmd.ExecuteNonQuery()
                End Using
            End Using
        Catch
            ' 列が既に存在する場合は無視
        End Try

        ' ===== 4. GPL合計 を降順に読み込み累積比率でランク付け =====
        Dim connStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & MyDB & ";"

        Dim totalGPL As Double = 0
        Try
            Using cn As New OleDbConnection(connStr)
                cn.Open()
                Using cmd As New OleDbCommand(
                    "SELECT Sum(GPL合計) FROM [T860_全出荷E集計]", cn)
                    Dim result As Object = cmd.ExecuteScalar()
                    If result IsNot Nothing AndAlso Not IsDBNull(result) Then
                        totalGPL = Convert.ToDouble(result)
                    End If
                End Using
            End Using
        Catch ex As Exception
            MsgBox("T860_全出荷E集計: GPL合計取得エラー: " & ex.Message)
            Return
        End Try

        If totalGPL = 0 Then
            MsgBox("T860_全出荷E集計: T860_全出荷E集計 の GPL合計が 0 です。処理を中止します。")
            Return
        End If

        Dim th_A1 As Double = EIQ_A1ランク
        Dim th_A2 As Double = EIQ_A1ランク + EIQ_A2ランク
        Dim th_B As Double = EIQ_A1ランク + EIQ_A2ランク + EIQ_Bランク
        Dim th_C As Double = EIQ_A1ランク + EIQ_A2ランク + EIQ_Bランク + EIQ_Cランク

        Try
            Using cn As New OleDbConnection(connStr)
                cn.Open()

                Dim 累積GPL As Double = 0

                Using selCmd As New OleDbCommand(
                    "SELECT 出荷先, GPL合計 FROM [T860_全出荷E集計] ORDER BY GPL合計 DESC", cn)
                    Using rdr As OleDbDataReader = selCmd.ExecuteReader()

                        Dim updCmd As New OleDbCommand(
                            "UPDATE [T860_全出荷E集計] SET [GPLEランク] = ? WHERE [出荷先] = ?", cn)
                        updCmd.Parameters.Add(New OleDbParameter("?", OleDbType.VarChar, 10))
                        updCmd.Parameters.Add(New OleDbParameter("?", OleDbType.VarChar, 20))

                        While rdr.Read()
                            Dim dest As String = rdr("出荷先").ToString()
                            Dim gpl As Double = If(IsDBNull(rdr("GPL合計")), 0, Convert.ToDouble(rdr("GPL合計")))

                            累積GPL += gpl
                            Dim 累積比率 As Double = (累積GPL / totalGPL) * 100.0

                            Dim rank As String
                            If 累積比率 <= th_A1 Then
                                rank = "GPLE_A1"
                            ElseIf 累積比率 <= th_A2 Then
                                rank = "GPLE_A2"
                            ElseIf 累積比率 <= th_B Then
                                rank = "GPLE_B"
                            ElseIf 累積比率 <= th_C Then
                                rank = "GPLE_C"
                            Else
                                rank = "GPLE_D"
                            End If

                            updCmd.Parameters(0).Value = rank
                            updCmd.Parameters(1).Value = dest
                            updCmd.ExecuteNonQuery()
                        End While

                        updCmd.Dispose()
                    End Using
                End Using

                cn.Close()
            End Using
        Catch ex As Exception
            MsgBox("T860_全出荷E集計: ランク更新エラー: " & ex.Message)
            Return
        End Try

        ' ===== 5. T800_計算対象 の GPLEランク 列に T860_全出荷E集計 のランクを反映 =====
        Try
            Using cn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & MyDB & ";")
                cn.Open()
                Try
                    Using addCmd As New OleDbCommand(
                        "ALTER TABLE [T800_計算対象] ADD COLUMN [GPLEランク] TEXT(10)", cn)
                        addCmd.ExecuteNonQuery()
                    End Using
                Catch
                    ' 既存の場合は無視
                End Try
            End Using
        Catch
        End Try

        MySQL = "UPDATE [T800_計算対象] " &
                "INNER JOIN [T860_全出荷E集計] ON [T800_計算対象].出荷先 = [T860_全出荷E集計].出荷先 " &
                "SET [T800_計算対象].[GPLEランク] = [T860_全出荷E集計].[GPLEランク];"
        SQL実行(MySQL)

    End Sub

    Public Sub T860_全出荷I集計()

        ' ===== 1. T860* テーブルを削除 =====
        DEL_table("T860_全出荷I*")
        ' ===== 2. T860_全出荷I集計 を アイテム × GPL合計 で作成 =====
        MySQL = "SELECT アイテム, Sum(GPL) AS GPL合計 INTO [T860_全出荷I集計] " &
                "FROM T800_計算対象 " &
                "GROUP BY アイテム"
        SQL実行(MySQL)

        ' ===== 3. GPLIランク 列を追加 =====
        Try
            Using cn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & MyDB & ";")
                cn.Open()
                Using addCmd As New OleDbCommand(
                    "ALTER TABLE [T860_全出荷I集計] ADD COLUMN [GPLIランク] TEXT(10)", cn)
                    addCmd.ExecuteNonQuery()
                End Using
            End Using
        Catch
            ' 列が既に存在する場合は無視
        End Try

        ' ===== 4. GPL合計 を降順に読み込み累積比率でランク付け =====
        Dim connStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & MyDB & ";"

        Dim totalGPL As Double = 0
        Try
            Using cn As New OleDbConnection(connStr)
                cn.Open()
                Using cmd As New OleDbCommand(
                    "SELECT Sum(GPL合計) FROM [T860_全出荷I集計]", cn)
                    Dim result As Object = cmd.ExecuteScalar()
                    If result IsNot Nothing AndAlso Not IsDBNull(result) Then
                        totalGPL = Convert.ToDouble(result)
                    End If
                End Using
            End Using
        Catch ex As Exception
            MsgBox("T860_全出荷I集計: GPL合計取得エラー: " & ex.Message)
            Return
        End Try

        If totalGPL = 0 Then
            MsgBox("T860_全出荷I集計: T860_全出荷I集計 の GPL合計が 0 です。処理を中止します。")
            Return
        End If

        Dim th_A1 As Double = EIQ_A1ランク
        Dim th_A2 As Double = EIQ_A1ランク + EIQ_A2ランク
        Dim th_B As Double = EIQ_A1ランク + EIQ_A2ランク + EIQ_Bランク
        Dim th_C As Double = EIQ_A1ランク + EIQ_A2ランク + EIQ_Bランク + EIQ_Cランク

        Try
            Using cn As New OleDbConnection(connStr)
                cn.Open()

                Dim 累積GPL As Double = 0

                Using selCmd As New OleDbCommand(
                    "SELECT アイテム, GPL合計 FROM [T860_全出荷I集計] ORDER BY GPL合計 DESC", cn)
                    Using rdr As OleDbDataReader = selCmd.ExecuteReader()

                        Dim updCmd As New OleDbCommand(
                            "UPDATE [T860_全出荷I集計] SET [GPLIランク] = ? WHERE [アイテム] = ?", cn)
                        updCmd.Parameters.Add(New OleDbParameter("?", OleDbType.VarChar, 10))
                        updCmd.Parameters.Add(New OleDbParameter("?", OleDbType.VarChar, 20))

                        While rdr.Read()
                            Dim item As String = rdr("アイテム").ToString()
                            Dim gpl As Double = If(IsDBNull(rdr("GPL合計")), 0, Convert.ToDouble(rdr("GPL合計")))

                            累積GPL += gpl
                            Dim 累積比率 As Double = (累積GPL / totalGPL) * 100.0

                            Dim rank As String
                            If 累積比率 <= th_A1 Then
                                rank = "GPLI_A1"
                            ElseIf 累積比率 <= th_A2 Then
                                rank = "GPLI_A2"
                            ElseIf 累積比率 <= th_B Then
                                rank = "GPLI_B"
                            ElseIf 累積比率 <= th_C Then
                                rank = "GPLI_C"
                            Else
                                rank = "GPLI_D"
                            End If

                            updCmd.Parameters(0).Value = rank
                            updCmd.Parameters(1).Value = item
                            updCmd.ExecuteNonQuery()
                        End While

                        updCmd.Dispose()
                    End Using
                End Using

                cn.Close()
            End Using
        Catch ex As Exception
            MsgBox("T860_全出荷I集計: ランク更新エラー: " & ex.Message)
            Return
        End Try

        ' ===== 5. T800_計算対象 の GPLIランク 列に T860_全出荷I集計 のランクを反映 =====
        Try
            Using cn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & MyDB & ";")
                cn.Open()
                Try
                    Using addCmd As New OleDbCommand(
                        "ALTER TABLE [T800_計算対象] ADD COLUMN [GPLIランク] TEXT(10)", cn)
                        addCmd.ExecuteNonQuery()
                    End Using
                Catch
                    ' 既存の場合は無視
                End Try
            End Using
        Catch
        End Try

        MySQL = "UPDATE [T800_計算対象] " &
                "INNER JOIN [T860_全出荷I集計] ON [T800_計算対象].アイテム = [T860_全出荷I集計].アイテム " &
                "SET [T800_計算対象].[GPLIランク] = [T860_全出荷I集計].[GPLIランク];"
        SQL実行(MySQL)


    End Sub

End Module
